package com.example.spring.exercise.dao;

import com.example.spring.exercise.service.dto.rouge.BrandData;
import com.example.spring.exercise.service.dto.rouge.BrandInfo;
import com.example.spring.exercise.service.dto.rouge.RougeInfo;
import com.example.spring.exercise.service.dto.rouge.ShopInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author : zhayh
 * @date : 2021-4-13 10:33
 * @description :
 */

@Repository
public class RougeDaoImpl implements RougeDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Override
    public List<BrandData> selectTopBrandData() {
        String sql = "select a.brand_id, b.brand_name, count(1) brand_rouge_count, " +
                "AVG(a.rouge_price) brand_rouge_price " +
                "from rouge_info a, brand_info b  where a.brand_id = b.id group by a.brand_id " +
                "order by AVG(a.rouge_price) DESC limit 0, 5";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BrandData.class));
    }

    @Override
    public Page<RougeInfo> selectRougeByBrandAndPrice(String brandName,
                                                      Float minPrice, Float maxPrice,
                                                      Integer pageIndex, Integer pageSize) {
        Integer start = pageIndex * pageSize;
        String sql = "select a.*,b.brand_name,b.brand_founder,b.brand_description,b.brand_country, " +
                "c.shop_name,c.shop_fans_count,c.shop_time " +
                "from rouge_info a " +
                "left join brand_info b on b.id=a.brand_id " +
                "left join shop_info c on c.id=a.shop_id " +
                "where a.rouge_name like concat('%', ?, '%') " +
                "and a.rouge_price>= ? and a.rouge_price <=?";
        String pageSql = "select * from (" + sql + ") tb limit ?, ?";
        List<RougeInfo> rougeInfos = jdbcTemplate.query(pageSql, new RougeRowMapper(),
                brandName, minPrice, maxPrice, start, pageSize);
        // 查询口红总数的sql
        String countSql = "select count(1) totalCount from (" + sql + ") tb";
        Long totalCount = jdbcTemplate.queryForObject(countSql, Long.class, brandName, minPrice, maxPrice);
        //使用PageImpl构造分页数据对象返回
        return new PageImpl<>(rougeInfos, PageRequest.of(pageIndex, pageSize), totalCount);
    }

    private static final class RougeRowMapper implements RowMapper<RougeInfo> {
        @Override
        public RougeInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            BrandInfo brandInfo = BrandInfo.builder()
                    .id(rs.getLong("brand_id"))
                    .brandName(rs.getString("brand_name"))
                    .brandFounder(rs.getString("brand_founder"))
                    .brandCountry(rs.getString("brand_country"))
                    .brandDescription(rs.getString("brand_description"))
                    .build();
            ShopInfo shopInfo = ShopInfo.builder()
                    .id(rs.getLong("shop_id"))
                    .shopName(rs.getString("shop_name"))
                    .shopFansCount(rs.getString("shop_fans_count"))
                    .shopTime(rs.getString("shop_time"))
                    .build();
            return RougeInfo.builder()
                    .id(rs.getLong("id"))
                    .rougeName(rs.getString("rouge_name"))
                    .rougePrice(rs.getFloat("rouge_price"))
                    .rougeShop(shopInfo)
                    .rougeBrand(brandInfo)
                    .build();
        }
    }


}
